package com.googlecode.lookie.imagedb.internal;

import java.io.File;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.datasource.SimpleDriverDataSource;
import org.springframework.richclient.application.ApplicationException;

import com.googlecode.lookie.platform.state.StateLocation;

/*
 * The database has 2 tables: - METADATA table - MEMORY table - DATA table - CACHED table
 */
public class HsqlImageDatabase
{

    private static final Logger logger = LoggerFactory.getLogger( HsqlImageDatabase.class );

    private SimpleDriverDataSource dataSource;

    private StateLocation stateLocation;

    protected HsqlImageDatabase( SimpleDriverDataSource dataSource )
    {
        this.dataSource = dataSource;
    }

    public void setStateLocation( StateLocation stateLocation )
    {
        this.stateLocation = stateLocation;
    }

    public void start()
    {
        File file = null;
        try
        {
            file = new File( stateLocation.getDirectory( "lookie.app.imagedb2" ), "imagedb" );
        }
        catch ( IOException e )
        {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        String url = String.format( "jdbc:hsqldb:file:%s;create=true", file.getAbsolutePath() );
        dataSource.setUrl( url );

        Connection connection = null;

        try
        {
            connection = dataSource.getConnection();

            DatabaseMetaData metaData = connection.getMetaData();

            PreparedStatement stmt = null;
            ResultSet rs = null;

            rs = metaData.getTables( null, null, "IMAGE", null );

            if ( !rs.next() )
            {
                stmt = connection.prepareStatement( "CREATE CACHED TABLE IMAGE (id VARCHAR PRIMARY KEY, data BINARY)" );
                stmt.execute();
            }

            rs = metaData.getTables( null, null, "IMAGE_DATA", null );

            if ( !rs.next() )
            {
                stmt = connection
                    .prepareStatement( "CREATE MEMORY TABLE IMAGE_DATA (id VARCHAR PRIMARY KEY, last_modified TIMESTAMP)" );
                stmt.execute();
            }

            rs.close();
        }
        catch ( SQLException e )
        {
            throw new ApplicationException( "error starting database" );
        }
        finally
        {
            if ( connection != null )
            {
                try
                {
                    connection.close();
                }
                catch ( SQLException e )
                {
                    logger.warn( "error closing connection after startup", e );
                }
            }
        }
    }

    public void stop()
    {
        Connection connection = null;

        try
        {
            connection = dataSource.getConnection();
        }
        catch ( SQLException e )
        {
            throw new ApplicationException( "could not get connection for closing database", e );
        }

        if ( connection != null )
        {
            try
            {
                connection.createStatement().execute( "SHUTDOWN" );
            }
            catch ( SQLException e )
            {
                throw new ApplicationException( "error closing database", e );
            }
            finally
            {
                try
                {
                    connection.close();
                }
                catch ( SQLException e )
                {
                    logger.warn( "error closing connection", e );
                }
            }
        }

    }
}
